import mysql.connector
import openpyxl

mydb = mysql.connector.connect(
    host="172.32.148.68",  # 数据库主机地址
    user="hebeitower",  # 数据库用户名
    port=3306,
    database="hebei-tower",  # 数据库名称 没有的话插入操作报错
    passwd="Hebeitower1!",  # 数据库密码
)

# mydb = mysql.connector.connect(
#     host="121.36.64.108",  # 数据库主机地址
#     user="hebei",  # 数据库用户名
#     port=8181,
#     database="hebei-tower",  # 数据库名称 没有的话插入操作报错
#     passwd="Hebeitower1!",  # 数据库密码
# )

# 根据配置信息生成操作对象
mycursor = mydb.cursor()
# 定义要执行的sql %s为占位符
sql = "INSERT INTO hb_cfg_scene_table_rel (scene_id,scene_type,column_name,table_name) VALUES (%s,%s,%s,%s)"
sql = "INSERT INTO hb_ai_table_col_rel (scene_id,ai_table_name,col_id,col_name) VALUES (%s,%s,%s,%s)"
sql = "INSERT INTO hb_xlsx_table_rel (scene_id,xlsx_name,sheet_name,col_num,table_name,table_col_name,xlsx_start_row) VALUES (%s,%s,%s,%s,%s,%s,%s)"
sql = "INSERT INTO hb_scene_table_col_cfg (col_id,col_name,field_id,scene_id,parent_col_id,width,high,order_id,col_level,scene_index,table_name,table_des) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
sql = "INSERT INTO hb_scene_verify_rule (rule_id,rule_name,remark,verify_class,target_table_name,columns_to_extract,update_mode) VALUES (%s,%s,%s,%s,%s,%s,%s)"
sql = "INSERT INTO hb_scene_verify_item (scene_id,rule_id,file_match_algo,op,params,remark,sort) VALUES (%s,%s,%s,%s,%s,%s,%s)"
sql = "INSERT INTO hb_scene_verify_file_algo (algo_id,match_pattern,group_for_order_id) VALUES (%s,%s,%s)"
sql = "INSERT INTO hb_ai_table_cols_rel (table_name,col_id,col_names,default_index,remark,col_need) VALUES (%s,%s,%s,%s,%s,%s)"
# workbook = openpyxl.load_workbook('hebei_dict_insert.xlsx')
workbook = openpyxl.load_workbook('hebei_dict.xlsx')

sheet = workbook["hb_cfg_scene_table_rel"]
sheet = workbook["hb_ai_table_col_rel"]
sheet = workbook["hb_xlsx_table_rel"]
sheet = workbook["hb_scene_table_col_cfg"]
sheet = workbook["hb_scene_verify_rule"]
sheet = workbook["hb_scene_verify_item"]
sheet = workbook["hb_scene_verify_file_algo"]
sheet = workbook["hb_ai_table_cols_rel"]
count = 0
for one_column_data in sheet.iter_rows():
    if count == 0:
        count = count + 1
        continue
    row_data = []
    for cell in one_column_data:
        row_data.append(cell.value)
        # print(cell.value)
    val = tuple(row_data)
    try:
        print(sql)
        mycursor.execute(sql, val)
        mydb.commit()
    except exa:
        print(exa)
        print("出错")
    print(val)
